<?php
namespace app\models;
use Yii;

class Article
{
	public static function getAll()
	{
		$articleQuery = (new \yii\db\Query());
		$articleList = $articleQuery->select("id, title, author, posterid, updatetime, submittime, publishtime, auditorid, audittime, priority, status")
				->from("tbl_article")->all();
		return Article::updateArticleList($articleList);
	}

	public static function getPublished($userid)
	{
		$queryMapping = array();
		$queryMapping['status'] = 3;
		$queryMapping['posterid'] = $userid;

		$articleQuery = (new \yii\db\Query());
		$articleList = $articleQuery->select("id, title, author, posterid, updatetime, submittime, publishtime, auditorid, audittime, priority, status")
				->from("tbl_article")->where($queryMapping)->all();
		return Article::updateArticleList($articleList);
	}

	public static function getAuditing($userid)
	{
		$queryMapping = array();
		$queryMapping['status'] = 2;
		$queryMapping['auditorid'] = $userid;

		$articleQuery = (new \yii\db\Query());
		$articleList = $articleQuery->select("id, title, author, posterid, updatetime, submittime, publishtime, auditorid, audittime, priority, status")
				->from("tbl_article")->where($queryMapping)->all();
		return Article::updateArticleList($articleList);
	}

	public static function getAllByCategory($categoryid = 0){

		$articleidQuery = (new \yii\db\Query());
		$articleidQuery->select("articleid")->from("tbl_article_category")->where("categoryid = :categoryid",[':categoryid' => $categoryid]);

		$articleQuery = (new \yii\db\Query());
		$articleList = $articleQuery->select("id, title, author, posterid, updatetime, submittime, publishtime, auditorid, audittime, priority, status")
			->from("tbl_article")->where(['id'=>$articleidQuery])->all();

		return Article::updateArticleList($articleList);
	}

	public static function getAllByOrder($order = ''){
		if (empty($order)){
			return self::getAll();
		}

		return self::getAll();
	}

	public static function getStatus($id)
	{
		$connection = Yii::$app->db;
		$sql = "SELECT status,posterid,auditorid FROM tbl_article WHERE id = $id";
		$query = $connection->createCommand($sql)->queryOne();

		return [
			'posterid' => $query['posterid'] , 
			'status' => $query['status'],
			'auditorid' => $query['auditorid'],
		];
	}

	public static function changeStatus($id,$status)
	{
		$connection = Yii::$app->db;
		$sql = "UPDATE tbl_article SET status = $status WHERE id = $id";
		$flag = $connection->createCommand($sql)->execute();
	}

	//提交审核
	public static function submitAudit($id,$auditorid,$cellphone)
	{
		$connection = Yii::$app->db;
		$submittime = date("Y-m-d H:i:s");
		$sql = "UPDATE tbl_article SET status = 2 , auditorid = '$auditorid' , submittime = '$submittime' WHERE id = $id";
		$flag = $connection->createCommand($sql)->execute();

		$connection->createCommand("INSERT INTO tbl_article_audit(articleid,auditorid,status,comment,audittime) VALUES($id,$auditorid,0,'','$submittime')")->execute();

		self::sendsms($cellphone,"您有一篇文章需要审核");
	}

	public static function Delete($id)
	{
		$connection = Yii::$app->db;
		$sql = "DELETE FROM tbl_article WHERE id = $id";
		$flag = $connection->createCommand($sql)->execute();

		if($flag)
		{
			$connection->createCommand()->delete("tbl_article_group",["articleid" => $id])->execute();
			$connection->createCommand()->delete("tbl_article_category",["articleid" => $id])->execute();
		}
	}

	public static function doAudit($id,$auditresult,$comment,$nextauditor,$auditorid,$cellphone)
	{
		$connection = Yii::$app->db;

		$audittime = date("Y-m-d H:i:s");

		if($auditresult == 1)
		{
			$sql = "INSERT INTO tbl_article_audit(articleid,auditorid,status,comment,audittime) VALUES($id,$auditorid,1,'$comment','$audittime')";
			$connection->createCommand($sql)->execute();

			//直接发布
			if($nextauditor == "")
			{
				$sql = "UPDATE tbl_article SET status = 3 ,audittime = '$audittime' , publishtime = '$audittime' WHERE id = $id";
				$connection->createCommand($sql)->execute();
				self::sendsms($cellphone,"您有一篇文章已经被发布了");
			}
			//下一位审核
			else
			{
				$sql = "UPDATE tbl_article SET status = 2 ,audittime = '$audittime',auditorid = $nextauditor WHERE id = $id";
				$connection->createCommand($sql)->execute();

				$sql = "INSERT INTO tbl_article_audit(articleid,auditorid,status,comment,audittime) VALUES($id,$nextauditor,0,'','$audittime')";
				$connection->createCommand($sql)->execute();

				self::sendsms($cellphone,"有一篇文章需要审核");
			}
			
		}
		else
		{
			//首次指定别人去审核
			$sql = "INSERT INTO tbl_article_audit(articleid,auditorid,status,comment,audittime) VALUES($id,$auditorid,2,'$comment','$audittime')";
			$connection->createCommand($sql)->execute();

			$sql = "UPDATE tbl_article SET status = 2 ,audittime = '$audittime' WHERE id = $id";
			$connection->createCommand($sql)->execute();

			self::sendsms($cellphone,"有一篇文章需要审核");
		}
	}

	public static function getArticleById($id)
	{
		$connection = Yii::$app->db;
		$sql = "SELECT * FROM tbl_article WHERE id = $id";
		$query = $connection->createCommand($sql)->queryOne();

		return $query;
	}

	public static function getGroupSelected($id)
	{
		$connection = Yii::$app->db;
		$sql = "SELECT groupid FROM tbl_article_group WHERE articleid = $id";
		$query = $connection->createCommand($sql)->queryAll();

		$arr = array();
		foreach ($query as $key => $value) {
			$arr[] = $value['groupid'];
		}

		return $arr;
	}

	public static function getCategorySelected($id)
	{
		$connection = Yii::$app->db;
		$sql = "SELECT categoryid FROM tbl_article_category WHERE articleid = $id";
		$query = $connection->createCommand($sql)->queryAll();

		$arr = array();
		foreach ($query as $key => $value) {
			$arr[] = $value['categoryid'];
		}

		return $arr;
	}

	private static function updateArticleList($articleList){

		if (!is_array($articleList) || (sizeof($articleList) < 0))
			return array();

		$connection = Yii::$app->db;

		$sql = "SELECT id, username FROM tbl_user";
		$userList = $connection->createCommand($sql)->queryAll();
		//首先获得所有用户信息
		$userMapping = array();
		foreach($userList as $userInfo){
			$userMapping[$userInfo['id']] = $userInfo['username'];
		}
		//得到用户id->用户姓名的对应

		$articleFinalList = array();
		$i = 0;

		foreach($articleList as $articleItem){
			$articleid = $articleItem['id'];
			if (empty($articleid))
				continue;

			$articleFinalList[$i] = $articleItem;
			$articleFinalList[$i]['postername'] = $userMapping[$articleItem['posterid']];
			$articleFinalList[$i]['auditorname'] = "";
			if(isset($userMapping[$articleItem['auditorid']]))
				$articleFinalList[$i]['auditorname'] = $userMapping[$articleItem['auditorid']];

			$queryids = (new \yii\db\Query());
			$query = (new \yii\db\Query());

			$queryids->select("categoryid")->from("tbl_article_category")->where("articleid = :articleid",[':articleid' => $articleid]);
			$categorynameList = $query->select("name")->distinct()->from("tbl_category")->where(["id" => $queryids])->all();

			$queryids->select("groupid")->from("tbl_article_group")->where("articleid = :articleid",[':articleid' =>$articleid]);
			$groupnameList = $query->select("name")->distinct()->from("tbl_group")->where(["id" =>$queryids])->all();

			$articleFinalList[$i]['category'] = Article::arr2String($categorynameList,"name");
			$articleFinalList[$i]['groups'] = Article::arr2String($groupnameList,"name");
			$i++;
		}

		return $articleFinalList;
	}

	private static function arr2String($arr,$key){
		$resultarray = array();
		foreach($arr as $item){
			array_push($resultarray,$item[$key]);
		}

		return implode(',',$resultarray);
	}

	private static function sendsms($cellphone, $content){

		if (empty($cellphone))
			return;

		$ch = curl_init();
		curl_setopt($ch, CURLOPT_URL, "http://sms-api.luosimao.com/v1/send.json");

		curl_setopt($ch, CURLOPT_HTTP_VERSION  , CURL_HTTP_VERSION_1_0 );
		curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
		curl_setopt($ch, CURLOPT_HEADER, FALSE);

		curl_setopt($ch, CURLOPT_HTTPAUTH , CURLAUTH_BASIC);
		curl_setopt($ch, CURLOPT_USERPWD  , 'api:key-3a5ecb10762d2c45776322a8f4761120');

		curl_setopt($ch, CURLOPT_POST, TRUE);
		curl_setopt($ch, CURLOPT_POSTFIELDS, array('mobile' => $cellphone,'message' => $content.'【交流合作中心】'));

		$res = curl_exec( $ch );
		curl_close( $ch );
	}

	public static function getAttachment($id)
	{
		$connection = Yii::$app->db;
		$query = $connection->createCommand("SELECT * FROM tbl_attachment WHERE articleid = $id")->queryAll();

		return $query;
	}
}